"""
Business Database Models
Core application data models for users, teams, and assignments
"""

from datetime import datetime
from typing import Optional
from sqlalchemy import (
    Column, String, DateTime, Text, Boolean, Integer, ForeignKey
)
from sqlalchemy.orm import relationship
from sqlalchemy.ext.hybrid import hybrid_property

from app.core.database import Base


class User(Base):
    """User model for authentication and basic user information"""
    __tablename__ = "users"

    id = Column(String, primary_key=True)
    email = Column(String, unique=True, nullable=False, index=True)
    password_hash = Column(String, nullable=False)
    name = Column(String, nullable=False)
    role = Column(String, nullable=False, default="MEMBER")
    is_active = Column(Boolean, default=True)
    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

    # Relationships
    team_memberships = relationship("TeamMember", foreign_keys="TeamMember.user_id", back_populates="user")
    created_teams = relationship("Team", back_populates="created_by_user")
    activity_logs = relationship("ActivityLog", back_populates="user")


class Team(Base):
    """Team model for organizing users and managing telemetry assignments"""
    __tablename__ = "teams"

    id = Column(String, primary_key=True)
    name = Column(String, nullable=False)
    description = Column(Text)
    created_by = Column(String, ForeignKey("users.id"), nullable=False)
    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

    # Relationships
    created_by_user = relationship("User", back_populates="created_teams")
    members = relationship("TeamMember", back_populates="team")
    file_assignments = relationship("TeamFileAssignment", back_populates="team")
    directories = relationship("Directory", back_populates="team")  # New: directory assignments
    activity_logs = relationship("ActivityLog", back_populates="team")


class TeamMember(Base):
    """Team membership model for managing team members and roles"""
    __tablename__ = "team_members"

    id = Column(String, primary_key=True)
    team_id = Column(String, ForeignKey("teams.id"), nullable=False)
    user_id = Column(String, ForeignKey("users.id"), nullable=False)
    role = Column(String, nullable=False, default="MEMBER")
    invited_by = Column(String, ForeignKey("users.id"))
    invited_at = Column(DateTime, default=datetime.utcnow)
    joined_at = Column(DateTime)
    status = Column(String, nullable=False, default="PENDING")  # PENDING, ACTIVE, INACTIVE

    # Relationships
    team = relationship("Team", back_populates="members")
    user = relationship("User", foreign_keys=[user_id], back_populates="team_memberships")
    inviter = relationship("User", foreign_keys=[invited_by])


class TeamFileAssignment(Base):
    """File path pattern assignments for teams"""
    __tablename__ = "team_file_assignments"

    id = Column(String, primary_key=True)
    team_id = Column(String, ForeignKey("teams.id"), nullable=False)
    file_pattern = Column(String, nullable=False)  # File path pattern (glob pattern)
    description = Column(Text)
    is_active = Column(Boolean, default=True)
    created_by = Column(String, ForeignKey("users.id"), nullable=False)
    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

    # Relationships
    team = relationship("Team", back_populates="file_assignments")
    creator = relationship("User", foreign_keys=[created_by])


class Directory(Base):
    """Directory model for organizing telemetry metrics by file paths"""
    __tablename__ = "directories"

    id = Column(Integer, primary_key=True, autoincrement=True)
    path = Column(String, nullable=False, unique=True, index=True)  # Full directory path
    name = Column(String, nullable=False)  # Directory name
    parent_id = Column(Integer, ForeignKey("directories.id"))  # Parent directory ID
    level = Column(Integer, default=0)  # Directory level depth
    team_id = Column(String, ForeignKey("teams.id"))
    assignment_confidence = Column(Integer, default=0)  # Confidence in team assignment
    assigned_at = Column(DateTime)
    assignment_method = Column(String)  # How assignment was determined
    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

    # Relationships
    parent = relationship("Directory", remote_side=[id], back_populates="children")
    children = relationship("Directory", back_populates="parent")
    team = relationship("Team", back_populates="directories")
    # Note: metrics relationship is defined in extractor.py to avoid circular imports


class ActivityLog(Base):
    """Activity logging model for auditing and tracking"""
    __tablename__ = "activity_log"

    id = Column(String, primary_key=True)
    user_id = Column(String, ForeignKey("users.id"), nullable=False)
    team_id = Column(String, ForeignKey("teams.id"))
    action = Column(String, nullable=False)  # CREATE, UPDATE, DELETE, ASSIGN, etc.
    resource_type = Column(String, nullable=False)  # TEAM, USER, ASSIGNMENT, etc.
    resource_id = Column(String)
    details = Column(Text)  # JSON string with additional details
    ip_address = Column(String)
    user_agent = Column(Text)
    created_at = Column(DateTime, default=datetime.utcnow)

    # Relationships
    user = relationship("User", back_populates="activity_logs")
    team = relationship("Team", back_populates="activity_logs")


class TelemetryAnalysis(Base):
    """Telemetry analysis status tracking model"""
    __tablename__ = "telemetry_analysis"

    id = Column(String, primary_key=True)
    metric_id = Column(Integer, nullable=False, index=True)  # Foreign key to extractor database
    analysis_status = Column(String, nullable=False, default="NOT_ANALYZED", index=True)
    analysis_notes = Column(Text)
    analyzed_by = Column(String, ForeignKey("users.id"))
    analyzed_at = Column(DateTime)
    team_id = Column(String, ForeignKey("teams.id"))
    assigned_team_id = Column(String, ForeignKey("teams.id"))
    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

    # Relationships
    analyzer = relationship("User", foreign_keys=[analyzed_by])
    team = relationship("Team", foreign_keys=[team_id])
    assigned_team = relationship("Team", foreign_keys=[assigned_team_id])

    @hybrid_property
    def is_analyzed(self):
        return self.analysis_status == "ANALYZED"

    @hybrid_property
    def needs_collection(self):
        return self.analysis_status == "NEEDS_COLLECTION"


# Database Views and Utility Functions
class MetricTeamAssignment(Base):
    """UMA metric to team assignment mapping model"""
    __tablename__ = "metric_team_assignments"

    id = Column(String, primary_key=True)
    metric_id = Column(Integer, nullable=False, index=True)
    team_name = Column(String, nullable=False, index=True)
    assignment_reason = Column(Text)
    assigned_at = Column(DateTime, default=datetime.utcnow)

    # Relationships
    # Note: This model connects to the extractor database tables via metric_id
    # but doesn't have a direct SQLAlchemy relationship due to cross-database references


# Database Views and Utility Functions
class TeamWithMemberCount:
    """Utility class for team queries with member counts"""

    def __init__(self, team: Team, member_count: int, assignment_count: int):
        self.team = team
        self.member_count = member_count
        self.assignment_count = assignment_count

    @hybrid_property
    def name(self):
        return self.team.name

    @hybrid_property
    def description(self):
        return self.team.description

    @hybrid_property
    def created_at(self):
        return self.team.created_at